package DAO;

import java.sql.*;
import javax.swing.*;
import connect.ConnectDB;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class SectionDAO {

    Connection conn;
    ResultSet rsSec = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;
    TreeMap<Integer, String> tmDep;

    public SectionDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataSec();
    }

    public void loadDataSec() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Sections";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsSec = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String checkSecId(String SecId) {
        try {
            loadDataSec();
            while (rsSec.next()) {
                if (rsSec.getString("SECTID").equals(SecId)) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            System.out.println("Error in checkSecId: " + e.getMessage());
            return "Error";
        }
    }

    public Vector loadSecToUpdate(String SecId) {
        Vector SecData = new Vector();
        try {
            rsSec.beforeFirst();
            while (rsSec.next()) {
                if (rsSec.getString("SECTID").equals(SecId)) {
                   SecData.add(rsSec.getString("SECTID"));
                   SecData.add(rsSec.getString("SECTNAME"));
                   SecData.add(rsSec.getString("SECTINCH"));
                   
                   if (tmDep.containsKey(rsSec.getInt("DEPTNO"))) {
                       String dep = rsSec.getInt("DEPTNO") + " - " + tmDep.get(rsSec.getInt("DEPTNO")) ;
                       SecData.add(dep);
                   }
                }
            }
            return SecData;
        } catch (Exception e) {
            System.out.println("Error in loadSecToUpdate: " + e.getMessage());
            return null;
        }
    }

    public boolean insertSec(Vector SecData) {
        try {
            int depNo = getDepNo(SecData);

            rsSec.moveToInsertRow();

            rsSec.updateString(1, SecData.get(0).toString());
            rsSec.updateString(2, SecData.get(1).toString());
            rsSec.updateString(3, SecData.get(2).toString());
            rsSec.updateInt(4, depNo);

            rsSec.insertRow();
            loadDataSec();
            return true;
        } catch (Exception e) {
            System.out.println("Error in insertSec: " + e.getMessage());
            return false;
        }
    }

    public boolean updateSec(Vector SecData) {
        try {
            int depNo = getDepNo(SecData);

            rsSec.beforeFirst();
            while (rsSec.next()) {
                if (rsSec.getString("SECTID").equals(SecData.get(0))) {

                    rsSec.updateString(2, SecData.get(1).toString());
                    rsSec.updateString(3, SecData.get(2).toString());
                    rsSec.updateInt(4, depNo);

                    rsSec.updateRow();
                    loadDataSec();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in updateSec: " + e.getMessage());
            return false;
        }
    }

    public void secLoadDepNo() {
        try {
            Vector<Integer> vDepNo = new Vector<Integer>();
            Vector<String> vDepName = new Vector<String>();
            String query = "SELECT * FROM Departments";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vDepNo.add(rs.getInt("DEPTNO"));
                vDepName.add(rs.getString("DEPTNAME").trim());
            }

            tmDep = new TreeMap<Integer, String>();
            for (int i = 0; i < vDepNo.size(); i++) {
                tmDep.put(vDepNo.get(i), vDepName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in secLoadDepNo: " + e.getMessage());
        }
    }

    public void loadSecCombobox(JComboBox cbDep) {
        secLoadDepNo();

        cbDep.removeAllItems();
        cbDep.addItem("Please select...");

        Set<Integer> keyDep = tmDep.keySet();
        for (int depNo : keyDep) {
            cbDep.addItem(depNo + " - " + tmDep.get(depNo));
        }

    }

    public int getDepNo(Vector SecData) {
        String depNo = (SecData.get(3).toString().split(" "))[0];
        return Integer.parseInt(depNo);
    }

    public DefaultTableModel loadTableSec() {
        try {
            loadDataSec();
            rsSec.beforeFirst();

            // Load column name
            ResultSetMetaData rsmd = rsSec.getMetaData();

            Vector colName = new Vector();

            colName.add(rsmd.getColumnName(1)); // Dept ID
            colName.add(rsmd.getColumnName(2)); // Dept Name
            colName.add(rsmd.getColumnName(3)); // Dept Name
            colName.add(rsmd.getColumnName(4)); // Dept Name

            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            // Load row data
            while (rsSec.next()) {
                Vector rowData = new Vector();
                for (int i = 1; i <= 4; i++) {
                    rowData.add(rsSec.getString(i));
                }
                dtm.addRow(rowData);
            }
            return dtm;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean deleteSec(String SecId) {
        try {
            rsSec.beforeFirst();
            while (rsSec.next()) {
                if (rsSec.getString("SECTID").trim().equals(SecId)) {
                    rsSec.deleteRow();
                    loadDataSec();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Have data in orther table - " + e.getMessage());
            return false;
        }
    }

    public void closeConnect() {
        try {
            if (rsSec != null) {
                rsSec.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }

            if (cdb.conn != null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Section");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
